package me.zhengjie.minbearwcs.dao.storage.inf;

import me.zhengjie.minbearwcs.entity.PO.wcs.outbound.stockUp.PalletMediumStorageSearchPo;
import me.zhengjie.minbearwcs.entity.PO.wcs.storage.MediumStorage;
import me.zhengjie.minbearwcs.entity.PO.wcs.storage.SmallStorage;
import me.zhengjie.minbearwcs.entity.PO.wcs.storage.type.StorageType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.security.SecureRandom;
import java.util.List;

@Mapper
public interface StorageDao {

    @Select("SELECT a.* FROM small_storage a\n" +
            "            left join medium_storage b\n" +
            "            on a.medium_storage_code = b.medium_storage_code\n" +
            "            left join large_storage c\n" +
            "            on b.large_storage_code = c.large_storage_code\n" +
            "            where \n" +
            "            c.large_storage_type = #{largeStorageType} and a.pallet_code is null and \n" +
            "            a.inbound_freeze_pallet is null and a.outbound_freeze_pallet is null\n" +
            "            order by c.order_num ,b.order_num,a.column_num \n" +
            "            limit 1")
    SmallStorage searchRandomEnterStorage(@Param("largeStorageType")StorageType storageType);


    @Select("SELECT c.large_storage_type FROM small_storage a\n" +
            "            left join medium_storage b\n" +
            "            on a.medium_storage_code = b.medium_storage_code\n" +
            "            left join large_storage c\n" +
            "            on b.large_storage_code = c.large_storage_code\n" +
            "            where\n" +
            "            a.small_storage_code = #{smallStorageCode}")
    StorageType selectLargeStorageTypeBySmallStorageCode(@Param("smallStorageCode")String smallStorageCode);


    @Select("SELECT c.large_storage_type FROM small_storage a\n" +
            "            left join medium_storage b\n" +
            "            on a.medium_storage_code = b.medium_storage_code\n" +
            "            left join large_storage c\n" +
            "            on b.large_storage_code = c.large_storage_code\n" +
            "            where\n" +
            "            a.qr_point = #{qrPoint}")
    StorageType selectLargeStorageTypeByQrPoint(@Param("qrPoint")String qrPoint);

    @Select("SELECT b.medium_storage_code FROM large_storage a\n" +
            "                        left join medium_storage b\n" +
            "                        on a.large_storage_code = b.large_storage_code\n" +
            "                        left join small_storage c\n" +
            "                        on b.medium_storage_code = c.medium_storage_code\n" +
            "                        where\n" +
            "                        a.large_storage_type = #{storageType} and c.is_entrance = 1\n" +
            "                        and c.pallet_code is null and c.inbound_freeze_pallet is null \n" +
            "                        and c.outbound_freeze_pallet is null\n" +
            "                        order by b.order_num")
    List<String> selectSingleEnterEmpty(@Param("storageType")StorageType storageType);


    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage c\n" +
            "on a.large_storage_code = c.large_storage_code\n" +
            "left join\n" +
            "(\n" +
            "select medium_storage_code, count(*) as pallet_num  from small_storage\n" +
            "where\n" +
            "pallet_code is  null and inbound_freeze_pallet is  null\n" +
            "and outbound_freeze_pallet is  null group by medium_storage_code\n" +
            ") b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "where\n" +
            "(c.large_storage_type = #{storageType1} or c.large_storage_type = #{storageType2})\n" +
            "and\n" +
            "(\n" +
            "(a.material_one = #{material} and a.inbound_order_one = #{inboundOrder} and a.batch_one = #{batch}\n" +
            "and a.material_two is null and a.inbound_order_two is null and a.batch_two is null)\n" +
            "or \n" +
            "(a.material_one is null and a.inbound_order_one is null and a.batch_one is null\n" +
            "and a.material_two = #{material} and a.inbound_order_two = #{inboundOrder} and a.batch_two = #{batch})\n" +
            ")\n" +
            "and\n" +
            "b.pallet_num > 0\n" +
            "order by c.order_num,a.order_num")
    List<String> selectCanUseInboundMediumStorage(
            @Param("storageType1")StorageType storageType1,
            @Param("storageType2")StorageType storageType2,
            @Param("material")String material,
            @Param("inboundOrder")String inboundOrder,
            @Param("batch")String batch
    );

    @Select("select distinct a.large_storage_code from medium_storage a\n" +
            "left join large_storage b\n" +
            "on a.large_storage_code = b.large_storage_code\n" +
            "where b.large_storage_type = #{storageType} and\n" +
            "(a.inbound_order_one = #{inboundOrderCode} or a.inbound_order_two = #{inboundOrderCode})\n" +
            "order by b.order_num")
    List<String> selectSameInboundOrderLargeStorage(@Param("storageType")StorageType storageType,@Param("inboundOrderCode")String inboundOrderCode);


    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage b\n" +
            "on a.large_storage_code = b.large_storage_code\n" +
            "where b.large_storage_code = #{largeStorage} and\n" +
            "a.material_one is null and a.inbound_order_one is null and a.batch_one is null\n" +
            "and a.material_two is null and a.inbound_order_two is null and a.batch_two is null\n" +
            "order by a.order_num\n" +
            "limit 1")
    String selectEmptyMediumStorageByLargeStorage(@Param("largeStorage")String largeStorage);

    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage b\n" +
            "on a.large_storage_code = b.large_storage_code\n" +
            "where b.large_storage_code = #{largeStorage} and\n" +
            "a.material_one is null and a.inbound_order_one is null and a.batch_one is null\n" +
            "and a.material_two is null and a.inbound_order_two is null and a.batch_two is null\n" +
            "order by a.order_num")
    List<String> selectEmptyMediumStorageListByLargeStorage(@Param("largeStorage")String largeStorage);


    @Select("select count(*) from medium_storage a\n" +
            "left join large_storage b\n" +
            "on a.large_storage_code = b.large_storage_code\n" +
            "where b.large_storage_type = #{storageType} \n" +
            "and\n" +
            "(a.material_one = #{materialCode} or a.material_two = #{materialCode})")
    Integer selectHasMaterail(@Param("storageType")StorageType storageType,@Param("materialCode")String materialCode);


    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage b\n" +
            "on a.large_storage_code = b.large_storage_code\n" +
            "where b.large_storage_type = #{storageType} \n" +
            "and a.material_one is null and a.inbound_order_one is null and a.batch_one is null\n" +
            "and a.material_two is null and a.inbound_order_two is null and a.batch_two is null\n" +
            "order by b.order_num,a.order_num\n" +
            "limit 1")
    String selectEmptyMediumStorageByStorageType(@Param("storageType")StorageType storageType);



    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage c\n" +
            "on a.large_storage_code = c.large_storage_code\n" +
            "left join\n" +
            "(\n" +
            "select medium_storage_code, count(*) as pallet_num  from small_storage\n" +
            "where\n" +
            "pallet_code is  null and inbound_freeze_pallet is  null\n" +
            "and outbound_freeze_pallet is  null group by medium_storage_code\n" +
            ") b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "where\n" +
            "(c.large_storage_type = #{largeStorageType1} or c.large_storage_type = #{largeStorageType2})\n" +
            "and\n" +
            "(\n" +
            "(a.material_one is not null and a.inbound_order_one is not null and a.batch_one is not null and\n" +
            "a.material_two = #{material} and a.inbound_order_two = #{inboundOrder} and a.batch_two = #{batch})\n" +
            "or \n" +
            "(a.material_one = #{material} and a.inbound_order_one = #{inboundOrder} and a.batch_one = #{batch} and\n" +
            "a.material_two is not null and a.inbound_order_two is not null and a.batch_two is not null)\n" +
            ")\n" +
            "and\n" +
            "b.pallet_num > 0\n" +
            "order by c.order_num,a.order_num")
    List<String> selectDoubleBatchByBatch(
            @Param("largeStorageType1")StorageType storageType1,
            @Param("largeStorageType2")StorageType storageType2,
            @Param("material")String material,
            @Param("inboundOrder")String inboundOrder,
            @Param("batch")String batch
    );


    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage c\n" +
            "on a.large_storage_code = c.large_storage_code\n" +
            "left join\n" +
            "(\n" +
            "select medium_storage_code, count(*) as pallet_num  from small_storage\n" +
            "where\n" +
            "pallet_code is  null and inbound_freeze_pallet is  null\n" +
            "and outbound_freeze_pallet is  null group by medium_storage_code\n" +
            ") b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "where\n" +
            "(c.large_storage_type = #{largeStorageType1} or c.large_storage_type = #{largeStorageType2})\n" +
            "and\n" +
            "(\n" +
            "(a.material_one is not null and a.inbound_order_one is not null and a.batch_one is not null and\n" +
            "a.material_two is null and a.inbound_order_two is null and a.batch_two is null)\n" +
            "or \n" +
            "(a.material_one is null and a.inbound_order_one is null and a.batch_one is null and\n" +
            "a.material_two is not null and a.inbound_order_two is not null and a.batch_two is not null)\n" +
            ")\n" +
            "and\n" +
            "b.pallet_num > 0\n" +
            "order by c.order_num,a.order_num")
    List<String> selectHasOneBatchInboundMediumStorage(
            @Param("largeStorageType1")StorageType storageType1,
            @Param("largeStorageType2")StorageType storageType2
    );


    @Select("select a.small_storage_code from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where c.large_storage_type = #{storageType} and\n" +
            "a.pallet_code is null and a.inbound_freeze_pallet is null and\n" +
            "a.outbound_freeze_pallet is null limit 1")
    String selectNullStorageByStoargeType(@Param("storageType")StorageType storageType);



    @Select("select c.large_storage_type from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where a.small_storage_code = #{smallStorage}")
    StorageType selectStorageTypeBySmallStorage(@Param("smallStorage")String smallStorage);


    @Select("select b.medium_storage_code from medium_storage b\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where (c.large_storage_type = #{storageType1} or c.large_storage_type = #{storageType2})\n" +
            "and b.outbound_order = #{outboundOrder}")
    List<String> selectCanOutboundMediumCode(
            @Param("storageType1") StorageType storageType1,
            @Param("storageType2") StorageType storageType2,
            @Param("outboundOrder") String outboundOrder
    );


    @Select("select a.medium_storage_code from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where\n" +
            "c.large_storage_code = #{largeStorageCode} and a.is_entrance = 1\n" +
            "and a.pallet_code is null and a.inbound_freeze_pallet is null \n" +
            "and a.outbound_freeze_pallet is null\n" +
            "order by b.order_num")
    List<String> selectSingleEnterEmptyByLargeStorage(@Param("largeStorageCode") String largeStorageCode);


    @Select("SELECT count(*) as num FROM small_storage \n" +
            "where medium_storage_code = #{mediumStorageCode} and \n" +
            "(pallet_code is not null or inbound_freeze_pallet is not null \n" +
            "or outbound_freeze_pallet is not null)")
    Integer selectPalletNumInMediumStorage(@Param("mediumStorageCode") String mediumStorageCode);


    @Select("select count(*) from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where c.large_storage_code = #{largeStorageCode} and \n" +
            "(a.pallet_code is not null or a.inbound_freeze_pallet is not null \n" +
            "or a.outbound_freeze_pallet is not null)")
    Integer selectPalletNumInLargeStorage(@Param("largeStorageCode") String largeStorageCode);



    @Select("select c.large_storage_code from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where a.small_storage_code = #{smallStorageCode}")
    String selectLargeStorageCodeBySmallStorageCode(@Param("smallStorageCode") String smallStorageCode);



    @Select("SELECT a.small_storage_code,a.is_entrance,a.column_num,a.outbound_freeze_pallet,\n" +
            "b.pallet_code,b.material_code,b.material_num,\n" +
            "b.inbound_order_code,b.batch_code,b.inbound_date\n" +
            "FROM small_storage a\n" +
            "left join pallet b\n" +
            "on a.pallet_code = b.pallet_code or a.outbound_freeze_pallet = b.pallet_code \n" +
            "where a.medium_storage_code = #{mediumStorage}\n" +
            "order by a.column_num")
    List<PalletMediumStorageSearchPo> selectPalletOrOutboundFreezePalletMediumStorage(@Param("mediumStorage") String mediumStorage);

}
